using System;
using System.Collections.Generic;
using System.Linq;
using sscore;
using DM4;
using System.Data;
using System.Data.SqlClient;
using sscore.Utils;
using System.Threading;

namespace DMN_MailListener
{
    public class WorkWithDbData
    {
        private readonly string _connectionString;
        private readonly LogHandler _writeLog;

        public WorkWithDbData(string connectionString, LogHandler writeLog = null)
        {
            _connectionString = connectionString;
            _writeLog = writeLog;
        }

        public void UpdateMailCheckStatus(int checkMailAccountId, LetterWorkParameters lwp)
        {            
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string sql = String.Format("UPDATE [{0}]"
                    + " SET [{1}] = {2}"
                    + " ,[{3}] = {4}"
                    + " ,[{5}] = {6}"
                    + " ,[{7}] = {8}"
                    + " ,[{9}] = '{10}'"
                    + " ,[{11}] = @{11}"
                    + " ,[{12}] =@{12}"
                    + " ,[{14}] = {15}"
                    + " WHERE [{13}] = @{13}"
                    , Tn.MaiLettersCheckResult
                    , CnMaiLettersCheckResult.LetterId, lwp.Letter.Id
                    , CnMaiLettersCheckResult.Timeout, lwp.Timeout
                    , CnMaiLettersCheckResult.AttemptsTotal, 1//lwp.AttemptsTotal
                    , CnMaiLettersCheckResult.AttemptsPerformed,1// lwp.AttemptsPerformed
                    , CnMaiLettersCheckResult.Result, lwp.Result
                    , CnMaiLettersCheckResult.ResultMessage
                    , CnMaiLettersCheckResult.Message
                    , CnMaiLettersCheckResult.Id
                    , CnMaiLettersCheckResult.CheckMailAccountId, checkMailAccountId);

                using (SqlCommand sc = new SqlCommand(sql, conn))
                {
                    SqlParameter paramResultMessage = sc.Parameters.Add("@" + CnMaiLettersCheckResult.ResultMessage, SqlDbType.NVarChar);
                    paramResultMessage.Value = lwp.ResultMessage;
                    paramResultMessage.Size = -1;

                    SqlParameter messageParameter = sc.Parameters.Add("@" + CnMaiLettersCheckResult.Message, SqlDbType.Binary);
                    if (lwp.MessageContent == null)
                        messageParameter.Value = DBNull.Value;
                    else
                    {
                        messageParameter.Value = lwp.MessageContent;
                        messageParameter.Size = lwp.MessageContent.Length;
                    }

                    SqlParameter dateParameter = sc.Parameters.Add("@" + CnMaiLettersCheckResult.Id, SqlDbType.DateTime);
                    dateParameter.Value = lwp.StartTime;


                    sc.ExecuteNonQuery();
                }

                if (lwp.Result == Consts.TRUE)
                {
                    foreach (MailLink ml in lwp.ListMailLink)
                    {
                        sql = String.Format("INSERT INTO [{0}]"
                            + " ([{1}]"
                            + " ,[{2}], [{3}], [{4}]"
                            + " ,[{5}])"
                            + " VALUES"
                            + "  (@{1}"
                            + " ,@{2},'{6}',@{4}"
                            + " ,'{7}')"
                            , Tn.MailLinks, CnMailLinks.LetterCheckResultId
                            , CnMailLinks.Link, CnMailLinks.User, CnMailLinks.Password
                            , CnMailLinks.Status
                            , ml.User
                            , ml.Status
                            );

                        using (SqlCommand sc = new SqlCommand(sql, conn))
                        {
                            SqlParameter dateParameter = sc.Parameters.Add("@" + CnMailLinks.LetterCheckResultId, SqlDbType.DateTime);
                            dateParameter.Value = lwp.StartTime;

                            SqlParameter paramLink = sc.Parameters.Add("@" + CnMailLinks.Link, SqlDbType.NVarChar);
                            paramLink.Value = ml.Link;
                            paramLink.Size = -1;

                            SqlParameter paramPass = sc.Parameters.Add("@" + CnMailLinks.Password, SqlDbType.NVarChar);
                            paramPass.Value = ml.Password;
                            paramPass.Size = -1;

                            sc.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        public List<MailListenerWorkerParameters> CreateMailListenerWorkerParameters(List<MailListenerParameters> mlParams)
        {
            List<MailListenerWorkerParameters> resultList = new List<MailListenerWorkerParameters>();
            foreach (MailListenerParameters paramItem in mlParams)         
                resultList.Add(CreateMailListenerWorkerParameters(paramItem));
            
            return resultList;
        }

        public MailListenerWorkerParameters CreateMailListenerWorkerParameters(MailListenerParameters mlParams)
        {
            MailListenerWorkerParameters mlwp = new MailListenerWorkerParameters()
            {               
                MailAccountForCheck = GetMailAccount(mlParams.MailAccountIdForCheck)
            };
            
            foreach (int lid in mlParams.LetterIds)
            {
                Thread.Sleep(10); //  StartTime = DateTime.Now,               
                LetterWorkParameters lwp = new LetterWorkParameters()
                {
                    StartTime = DateTime.Now,
                    Letter = GetLetter(lid),
                    Timeout = mlParams.Timeout,
                    //AttemptsTotal = mlParams.Attempts
                };

                lwp.Sender = GetMailSender(lwp.Letter.SenderId);

                List<MailRule> mrlist = GetMailRulesByLetterId(lwp.Letter.Id);
                foreach (MailRule mr in mrlist)
                    lwp.MailRules.Add(new MailRuleResult() { Id = mr.Id, Rule = mr.Rule, RuleType = mr.RuleType });

                mlwp.LettersWorkParam.Add(lwp);
            }

            InsertMailListenerParameters(mlwp);

            return mlwp;
        }

        private void InsertMailListenerParameters(MailListenerWorkerParameters mlwp)
        {
            try
            {
                foreach (LetterWorkParameters lwp in mlwp.LettersWorkParam)
                {
                    string sql =
                        String.Format("if not exists (select * from [{0}] where [{1}] = @{1}) "
                        + "INSERT INTO [{0}] "
                        + "([{1}], [{2}], [{3}],"
                        + "[{4}],[{5}],[{6}],"
                        + "[{7}],[{8}],"
                        + "[{9}]) "
                        + "VALUES (@{1}, {10}, {11},"
                        + "{12},{13},{14},"
                        + "'{15}',@{8},"
                        + "@{9})",
                        Tn.MaiLettersCheckResult,
                        CnMaiLettersCheckResult.Id, CnMaiLettersCheckResult.CheckMailAccountId, CnMaiLettersCheckResult.LetterId,
                        CnMaiLettersCheckResult.Timeout, CnMaiLettersCheckResult.AttemptsTotal, CnMaiLettersCheckResult.AttemptsPerformed,
                        CnMaiLettersCheckResult.Result, CnMaiLettersCheckResult.ResultMessage,
                        CnMaiLettersCheckResult.Message,
                        mlwp.MailAccountForCheck.Id, lwp.Letter.Id,
                        lwp.Timeout, 1,0,// lwp.AttemptsTotal, lwp.AttemptsPerformed,
                        lwp.Result
                       );

                    using (SqlConnection conn = new SqlConnection(_connectionString))
                    {
                        conn.Open();
                        using (SqlCommand sc = new SqlCommand(sql, conn))
                        {
                            SqlParameter dateParameter = sc.Parameters.Add("@" + CnMaiLettersCheckResult.Id, SqlDbType.DateTime);
                            dateParameter.Value = lwp.StartTime;

                            SqlParameter messageParameter = sc.Parameters.Add("@" + CnMaiLettersCheckResult.Message, SqlDbType.Binary);
                            if (lwp.MessageContent == null)
                                messageParameter.Value = DBNull.Value;
                            else
                            {
                                messageParameter.Value = lwp.MessageContent;
                                messageParameter.Size = lwp.MessageContent.Length;
                            }

                            SqlParameter resultMessageParam = sc.Parameters.Add("@" + CnMaiLettersCheckResult.ResultMessage, SqlDbType.NVarChar);
                            resultMessageParam.Value = lwp.ResultMessage;
                            resultMessageParam.Size = -1;

                            sc.ExecuteNonQuery();
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                WriteLog(String.Format("Error occurred while InsertMailListenerParameters function execute. {0}", ex.Message), MessageType.Error);
            }
        }

        public List<MailListenerWorkerParameters> GetNotEndedCheck()
        {
            List<MailListenerWorkerParameters> resultList = new List<MailListenerWorkerParameters>();

            string sql = String.Format("select * from {0} where [{1}] != '{4}'  and [{2}] < [{3}]  and  [{3}] != 0 order by [{5}] "
               , Tn.MaiLettersCheckResult
               , CnMaiLettersCheckResult.Result
               , CnMaiLettersCheckResult.AttemptsPerformed, CnMaiLettersCheckResult.AttemptsTotal
               , Consts.TRUE
               , CnMaiLettersCheckResult.CheckMailAccountId);
            WriteLog(sql);

            MailListenerWorkerParameters mlwp = null;
            int checkMailAccountId = -1;
            try
            {
                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    using (SqlCommand sel = new SqlCommand(sql, conn))
                    {
                        using (SqlDataReader reader = sel.ExecuteReader())
                        {

                            if (reader.HasRows)
                                while (reader.Read())
                                {

                                    if (checkMailAccountId != SysUtils.ConvertFromDBVal<int>(reader[CnMaiLettersCheckResult.CheckMailAccountId]))
                                    {
                                        if (mlwp != null)
                                            resultList.Add(mlwp);

                                        checkMailAccountId = SysUtils.ConvertFromDBVal<int>(reader[CnMaiLettersCheckResult.CheckMailAccountId]);
                                        mlwp = new MailListenerWorkerParameters()
                                       {                                           
                                           MailAccountForCheck = GetMailAccount(checkMailAccountId)
                                       };
                                    }

                                    LetterWorkParameters lwp = new LetterWorkParameters()
                                    {
                                        StartTime = SysUtils.ConvertFromDBVal<DateTime>(reader[CnMaiLettersCheckResult.Id]),
                                        Letter = GetLetter(SysUtils.ConvertFromDBVal<int>(reader[CnMaiLettersCheckResult.LetterId])),
                                        Timeout = SysUtils.ConvertFromDBVal<int>(reader[CnMaiLettersCheckResult.Timeout]),
                                        //AttemptsTotal = SysUtils.ConvertFromDBVal<int>(reader[CnMaiLettersCheckResult.AttemptsTotal]),
                                       // AttemptsPerformed = SysUtils.ConvertFromDBVal<int>(reader[CnMaiLettersCheckResult.AttemptsPerformed])
                                    };

                                    lwp.Sender = GetMailSender(lwp.Letter.SenderId);

                                    List<MailRule> mrlist = GetMailRulesByLetterId(lwp.Letter.Id);
                                    foreach (MailRule mr in mrlist)
                                        lwp.MailRules.Add(new MailRuleResult() { Id = mr.Id, Rule = mr.Rule, RuleType = mr.RuleType });

                                    mlwp.LettersWorkParam.Add(lwp);
                                }

                            if (mlwp != null)
                                resultList.Add(mlwp);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(String.Format("Error occurred while GetNotEndedCheck function execute. {0}", ex.Message), MessageType.Error);
            }
            return resultList;
        }

        private List<MailRule> GetMailRulesByLetterId(int letterId)
        {
            List<MailRule> resultList = new List<MailRule>();

            string sql = String.Format("SELECT [{0}].* FROM [{0}], [{1}] where [{0}].[{2}] = [{1}].[{3}] and [{1}].[{4}] = {5}"
                , Tn.MailRules, Tn.MaiLettersRules, CnMailRules.Id, CnMailLettersRules.RuleId, CnMailLettersRules.LetterId, letterId);

            WriteLog(sql);
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand sel = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        if (reader.HasRows)
                            try
                            {
                                while (reader.Read())
                                {
                                    MailRule mr = new MailRule()
                                    {
                                        Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailRules.Id]),
                                        Rule = SysUtils.ConvertFromDBVal<string>(reader[CnMailRules.Rule]),           
                                        RuleName = SysUtils.ConvertFromDBVal<string>(reader[CnMailRules.RuleName]),
                                        RuleType = MailRuleValues.Dict.First(kvp => kvp.Value.Equals(SysUtils.ConvertFromDBVal<string>(reader[CnMailRules.RuleType]))).Key
                                    };

                                    resultList.Add(mr);
                                }
                            }
                            catch (Exception ex)
                            {
                                WriteLog(String.Format("Error occurred while GetMailRulesByLetterId execute by Id [{0}]: {1}", letterId, ex.Message), MessageType.Error);
                            }
                    }
                }
            }

            return resultList;
        }

        private Letter GetLetter(int id)
        {
            Letter l = new Letter();

            string sql = String.Format("select * from {0} where {1} = {2}", Tn.MaiLetters, CnMailLetters.Id, id);

            WriteLog(sql);
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand sel = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        if (reader.HasRows)
                            try
                            {
                                reader.Read();
                                l.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailLetters.Id]);
                                l.SenderId = SysUtils.ConvertFromDBVal<int>(reader[CnMailLetters.SenderId]);
                                l.Description = SysUtils.ConvertFromDBVal<string>(reader[CnMailLetters.Description]);
                            }
                            catch (Exception ex)
                            {
                                WriteLog(String.Format("Error occurred while GetLetter execute by Id [{0}]: {1}", id, ex.Message), MessageType.Error);
                            }
                    }
                }
            }

            return l;
        }

        public MailAccount GetMailAccount(int id)
        {
            MailAccount ma = new MailAccount();

            string sql = String.Format("select * from {0} where {1} = {2}", Tn.MailAccounts, CnMailAccounts.Id, id);

            WriteLog(sql);

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand sel = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        if (reader.HasRows)
                            try
                            {
                                reader.Read();
                                ma.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailAccounts.Id]);
                                ma.Mail = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.Mail]);
                                ma.SendServer = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendServer]);
                                ma.SendPort = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendPort]);
                                ma.SendUser = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendUser]);
                                ma.SendPassword = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendPassword]);
                                ma.SendUseSSL = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendUseSsl]) == Consts.TRUE;

                                ma.ReceiveServer = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveServer]);
                                ma.ReceivePort = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceivePort]);
                                ma.ReceiveUser = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveUser]);
                                ma.ReceivePassword = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceivePassword]);
                                ma.ReceiveUseSSL = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveUseSsl]) == Consts.TRUE;
                            }
                            catch (Exception ex)
                            {
                                WriteLog(String.Format("Error occurred while GetMailAccount with Id [{0}]: {1}", id, ex.Message), MessageType.Error);
                            }
                    }
                }
            }
            return ma;
        }

        public MailSender GetMailSender(int id, string name = "")
        {
            const string funcName = "GetMailSender";

            MailSender si = new MailSender();

            string sql = name == ""
                ? String.Format("select * from {0} where {1} = {2}", Tn.MailSenders, CnMailSenders.Id, id)
                : String.Format("select * from {0} where LTRIM(RTRIM(UPPER({1}))) = '{2}'", Tn.MailSenders, CnMailSenders.Mail, name.Trim().ToUpper());

            WriteLog(sql);
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand sel = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        if (reader.HasRows)
                            try
                            {
                                reader.Read();
                                si.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailSenders.Id]);
                                si.Mail = SysUtils.ConvertFromDBVal<string>(reader[CnMailSenders.Mail]);
                            }
                            catch (Exception ex)
                            {
                                WriteLog(String.Format("Error occurred while {0}: Id [{1}], [{2}] {3}", funcName, id, name, ex.Message), MessageType.Error);
                            }
                    }
                }
            }

            return si;
        }

        private void WriteLog(string aMessage, MessageType type = MessageType.Info)
        {
            if (_writeLog != null)
                _writeLog(aMessage, type);
        }
    }
}
